-- 用户角色枚举类型（扩展）
CREATE TYPE hc_user_role_type AS ENUM (
    'super_admin',     -- 超级管理员
    'system_admin',    -- 系统管理员
    'content_manager', -- 内容管理员
    'data_analyst',    -- 数据分析师
    'health_worker',   -- 健康工作者
    'standard_user',   -- 标准用户
    'limited_user'     -- 受限用户
);

-- 用户角色表
CREATE TABLE hc_user_roles (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    role_type hc_user_role_type NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL, -- 角色名称（中文）
    description TEXT, -- 角色描述
    permissions JSONB DEFAULT '{}'::jsonb, -- 角色权限配置
    is_system_role BOOLEAN DEFAULT FALSE, -- 是否为系统内置角色
    is_active BOOLEAN DEFAULT TRUE,
    
    -- 逻辑删除字段
    is_deleted BOOLEAN DEFAULT FALSE NOT NULL,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES auth.users(id),
    
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 用户角色关联表
CREATE TABLE hc_user_role_assignments (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
    role_id UUID REFERENCES hc_user_roles(id) ON DELETE CASCADE NOT NULL,
    dnode_id UUID REFERENCES hc_dnodes(id) ON DELETE CASCADE, -- 可选的dnode范围限制
    assigned_by UUID REFERENCES auth.users(id) NOT NULL,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE, -- 角色过期时间
    
    -- 逻辑删除字段
    is_deleted BOOLEAN DEFAULT FALSE NOT NULL,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES auth.users(id),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 同一用户在同一个dnode中不能重复分配同一角色
    UNIQUE(user_id, role_id, dnode_id)
);

-- 身份证类型枚举
CREATE TYPE hc_id_card_type AS ENUM (
    'id_card',         -- 居民身份证
    'passport',        -- 护照
    'driving_license', -- 驾驶证
    'officer_card',    -- 军官证
    'other'            -- 其他证件
);


-- 用户个人资料表（完整版）
CREATE TABLE hc_user_profiles (
    -- 基础信息字段
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    full_name TEXT,
    avatar_url TEXT,
    default_dnode_id UUID REFERENCES hc_dnodes(id), -- 默认组织
    
    -- 身份证信息字段
    id_card_type hc_id_card_type, -- 身份证类型
    id_card_number VARCHAR(50), -- 身份证号码
    id_card_verified BOOLEAN DEFAULT FALSE, -- 身份证是否已验证
    id_card_verified_at TIMESTAMP WITH TIME ZONE, -- 验证时间
    id_card_verified_by UUID REFERENCES auth.users(id), -- 验证人
    
    -- 健康信息字段
    health_code VARCHAR(100), -- 健康编码
    health_code_expires_at TIMESTAMP WITH TIME ZONE, -- 健康编码过期时间
    health_code_updated_at TIMESTAMP WITH TIME ZONE, -- 健康状态更新时间
    health_notes TEXT, -- 健康备注信息
    
    -- 隐私和合规字段
    data_consent_given BOOLEAN DEFAULT FALSE, -- 数据收集同意
    data_consent_given_at TIMESTAMP WITH TIME ZONE, -- 同意时间
    privacy_level VARCHAR(20) DEFAULT 'standard', -- 隐私级别
    
    -- 逻辑删除字段
    is_deleted BOOLEAN DEFAULT FALSE NOT NULL,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES auth.users(id),
    
    -- 时间戳字段
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建相关索引
-- CREATE INDEX idx_profiles_username ON profiles(username) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_default_dnode ON profiles(default_dnode_id) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_id_card_type ON profiles(id_card_type) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_id_card_verified ON profiles(id_card_verified) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_health_status ON profiles(health_status) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_health_code_expires ON profiles(health_code_expires_at) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_created_at ON profiles(created_at) WHERE is_deleted = FALSE;

-- 身份证号码唯一约束（仅对已验证的身份证）
-- CREATE UNIQUE INDEX idx_profiles_verified_id_card 
-- ON profiles(id_card_number) 
-- WHERE id_card_verified = TRUE AND is_deleted = FALSE;

-- 健康编码索引
-- CREATE INDEX idx_profiles_health_code 
-- ON profiles(health_code) 
-- WHERE is_deleted = FALSE;

-- 更新时间戳触发器
-- CREATE OR REPLACE FUNCTION update_profiles_updated_at()
-- RETURNS TRIGGER AS $$
-- BEGIN
--     NEW.updated_at = NOW();
--     RETURN NEW;
-- END;
-- $$ language 'plpgsql';

-- CREATE TRIGGER trigger_update_profiles_updated_at
--     BEFORE UPDATE ON profiles
--     FOR EACH ROW
--     EXECUTE FUNCTION update_profiles_updated_at();

-- 插入系统默认角色
INSERT INTO hc_user_roles (role_type, name, description, is_system_role, permissions) VALUES
('super_admin', '超级管理员', '系统最高权限管理员，拥有所有权限', TRUE, '{"all": true}'),
('system_admin', '系统管理员', '管理系统设置和用户管理', TRUE, '{"user_manage": true, "system_config": true}'),
('content_manager', '内容管理员', '管理平台内容和数据', TRUE, '{"content_manage": true, "data_view": true}'),
('data_analyst', '数据分析师', '查看和分析系统数据', TRUE, '{"data_analyze": true, "report_view": true}'),
('health_worker', '健康工作者', '管理健康相关数据和用户', TRUE, '{"health_data_manage": true, "user_health_view": true}'),
('standard_user', '标准用户', '普通用户权限', TRUE, '{"basic_operations": true, "self_manage": true}'),
('limited_user', '受限用户', '权限受限的用户', TRUE, '{"limited_operations": true}');

-- -- 用户角色表索引
-- CREATE INDEX idx_user_roles_type ON user_roles(role_type) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_user_roles_system ON user_roles(is_system_role) WHERE is_deleted = FALSE;

-- -- 用户角色分配表索引
-- CREATE INDEX idx_user_role_assignments_user ON user_role_assignments(user_id) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_user_role_assignments_role ON user_role_assignments(role_id) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_user_role_assignments_dnode ON user_role_assignments(dnode_id) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_user_role_assignments_expires ON user_role_assignments(expires_at) WHERE is_deleted = FALSE;

-- -- profiles 表新字段索引
-- CREATE INDEX idx_profiles_id_card_type ON profiles(id_card_type) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_id_card_verified ON profiles(id_card_verified) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_health_status ON profiles(health_status) WHERE is_deleted = FALSE;
-- CREATE INDEX idx_profiles_health_code_expires ON profiles(health_code_expires_at) WHERE is_deleted = FALSE;